﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace EnterpriseFund.DAL
{
    /// <summary>
    /// 数据访问类:QueryFIssueNo
    /// </summary>
    public partial class QueryFIssueNo
    {
        public QueryFIssueNo()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string FAllowanceFileID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_AllowanceFile");
            strSql.Append(" where FAllowanceFileID=@FAllowanceFileID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FAllowanceFileID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FAllowanceFileID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string FAllowanceFileID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_AllowanceFile ");
            strSql.Append(" where FAllowanceFileID=@FAllowanceFileID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FAllowanceFileID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FAllowanceFileID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string FAllowanceFileIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_AllowanceFile ");
            strSql.Append(" where FAllowanceFileID in (" + FAllowanceFileIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.QueryFIssueNo GetModel(string FAllowanceFileID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FAllowanceFileID,FPeriod,FFileName,FFileNo,FFileTransDate,FNote,FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FFinancePropNo,FYear from t_AllowanceFile ");
            strSql.Append(" where FAllowanceFileID=@FAllowanceFileID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FAllowanceFileID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FAllowanceFileID;

            EnterpriseFund.Model.QueryFIssueNo model = new EnterpriseFund.Model.QueryFIssueNo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.QueryFIssueNo DataRowToModel(DataRow row)
        {
            EnterpriseFund.Model.QueryFIssueNo model = new EnterpriseFund.Model.QueryFIssueNo();
            if (row != null)
            {
                if (row["FAllowanceFileID"] != null)
                {
                    model.FAllowanceFileID = row["FAllowanceFileID"].ToString();
                }
                if (row["FPeriod"] != null && row["FPeriod"].ToString() != "")
                {
                    model.FPeriod = int.Parse(row["FPeriod"].ToString());
                }
                if (row["FFileName"] != null)
                {
                    model.FFileName = row["FFileName"].ToString();
                }
                if (row["FFileNo"] != null)
                {
                    model.FFileNo = row["FFileNo"].ToString();
                }
                if (row["FFileTransDate"] != null && row["FFileTransDate"].ToString() != "")
                {
                    model.FFileTransDate = DateTime.Parse(row["FFileTransDate"].ToString());
                }
                if (row["FNote"] != null)
                {
                    model.FNote = row["FNote"].ToString();
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FInsEmpNo"] != null)
                {
                    model.FInsEmpNo = row["FInsEmpNo"].ToString();
                }
                if (row["FInsDeptNo"] != null)
                {
                    model.FInsDeptNo = row["FInsDeptNo"].ToString();
                }
                if (row["FInsDate"] != null && row["FInsDate"].ToString() != "")
                {
                    model.FInsDate = DateTime.Parse(row["FInsDate"].ToString());
                }
                if (row["FInsIP"] != null)
                {
                    model.FInsIP = row["FInsIP"].ToString();
                }
                if (row["FUpdEmplNo"] != null)
                {
                    model.FUpdEmplNo = row["FUpdEmplNo"].ToString();
                }
                if (row["FUpdDeptNo"] != null)
                {
                    model.FUpdDeptNo = row["FUpdDeptNo"].ToString();
                }
                if (row["FUpdDate"] != null && row["FUpdDate"].ToString() != "")
                {
                    model.FUpdDate = DateTime.Parse(row["FUpdDate"].ToString());
                }
                if (row["FUpdIP"] != null)
                {
                    model.FUpdIP = row["FUpdIP"].ToString();
                }
                if (row["FFinancePropNo"] != null)
                {
                    model.FFinancePropNo = row["FFinancePropNo"].ToString();
                }
                if (row["FYear"] != null && row["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(row["FYear"].ToString());
                }
                if (row["FFinancePropName"] != null)
                {
                    model.FFinancePropName = row["FFinancePropName"].ToString();
                }
                if (row["NSRDZDAH"] != null)
                {
                    model.NSRDZDAH = row["NSRDZDAH"].ToString();
                }
                if (row["NSRBM"] != null)
                {
                    model.NSRBM = row["NSRBM"].ToString();
                }
                if (row["NSRMC"] != null)
                {
                    model.NSRMC = row["NSRMC"].ToString();
                }
                if (row["FAmount"] != null & !string.IsNullOrEmpty(row["FAmount"].ToString()))
                {
                    model.FAmount = decimal.Parse(row["FAmount"].ToString());
                }
                if (row["NSRSBH"] != null)
                {
                    model.NSRSBH = row["NSRSBH"].ToString();
                }
                if (row["FIssueNo"] != null)
                {
                    model.FIssueNo = row["FIssueNo"].ToString();
                }
                if (row["FFinancePropShortName"] != null)
                {
                    model.FFinancePropShortName = row["FFinancePropShortName"].ToString();
                }
                
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from (
select f.FAllowanceFileEntryID,
FAllowanceFileID,f.NSRDZDAH,sum(FFactAllowanceAmt) FAmount,FIssueID
 from fund.t_AllowanceFileEntry 
right join  (
 select a.FAllowanceIssueEntryID,a.FAllowanceFileEntryID,a.NSRDZDAH,a.FAllowanceBalAmt,a.FAllowancePeriodAmt,a.FFactAllowanceAmt,a.FIssueID from fund.t_AllowanceIssueEntry a
 union all  select null,b.FAllowanceFileEntryID,b.NSRDZDAH,b.FAdjustAmount,0,b.FAdjustAmount,b.FIssueID from fund.t_SpecificAdjust b
 )f  on f.FAllowanceFileEntryID=fund.t_AllowanceFileEntry .FAllowanceFileEntryID
group by f.FAllowanceFileEntryID,FAllowanceFileID,f.NSRDZDAH,FIssueID
) as t1
left join  fund.t_AllowanceFile  on  t1.FAllowanceFileID=fund.t_AllowanceFile.FAllowanceFileID
join (select FIssueNo,FIssueID from fund.t_AllowanceIssue ) as t2
on t1.FIssueID=t2.FIssueID
join 
(select NSRDZDAH,NSRBM,NSRMC,NSRSBH from ltais.DJ_NSRXX ) as t3
on t1.NSRDZDAH=t3.NSRDZDAH
left join  fund.t_FinanceProp
on fund.t_AllowanceFile.FFinancePropNo=fund.t_FinanceProp.FFinancePropNo
where (1=1) 
  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FAllowanceFileID,FPeriod,FFileName,FFileNo,FFileTransDate,FNote,FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FFinancePropNo,FYear ");
            strSql.Append(" FROM t_AllowanceFile ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_AllowanceFile ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FAllowanceFileID desc");
            }
            strSql.Append(")AS Row, T.*  from t_AllowanceFile T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "t_AllowanceFile";
            parameters[1].Value = "FAllowanceFileID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

